--------------------------------------------------------
--  File created - Wednesday-June-04-2014   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Sequence BOOKS_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "LIBRARY"."BOOKS_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 117 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence PUBLICATIONS_TYPE_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "LIBRARY"."PUBLICATIONS_TYPE_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Table BOOKS
--------------------------------------------------------

  CREATE TABLE "LIBRARY"."BOOKS" 
   (	"BOOK_ID" NUMBER, 
	"BOOK_NAME" VARCHAR2(4000 BYTE), 
	"BOOK_AUTHOR" VARCHAR2(4000 BYTE), 
	"NUMBER_OF_PAGES" NUMBER, 
	"BOOK_FORMAT" VARCHAR2(4000 BYTE), 
	"GENRE_ID" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table GENRES
--------------------------------------------------------

  CREATE TABLE "LIBRARY"."GENRES" 
   (	"GENRE_ID" NUMBER, 
	"GENRE" VARCHAR2(40 BYTE), 
	"PUBLICATION_TYPE_ID" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table LIBRARY
--------------------------------------------------------

  CREATE TABLE "LIBRARY"."LIBRARY" 
   (	"LIBRARY_ID" NUMBER, 
	"PUBLICATION_TYPE_ID" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table PUBLICATION_TYPE
--------------------------------------------------------

  CREATE TABLE "LIBRARY"."PUBLICATION_TYPE" 
   (	"PUBLICATION_TYPE_ID" NUMBER, 
	"TYPE_NAME" VARCHAR2(40 BYTE)
   ) ;
REM INSERTING into LIBRARY.BOOKS
SET DEFINE OFF;
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (104,'rr','er',56,'56',19);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (116,'1','2',2,'2',19);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (79,'rther','trher',76,'fdvd',8);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (80,'re','wreg',67,'gfds',8);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (81,'rth','trhe',78,'rew',8);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (4,'Anna Karenina','Lev Tolstoi',864,'pdf',1);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (5,'The master and Margarita','Mikhail Bulgakov',418,'pdf',1);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (6,'The black ciry','Boris Akunin',352,'pdf',2);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (7,'The Winter Queen','Boris Akunin',240,'docx',2);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (8,'Spark of Life','Erich Maria Remarque',224,'docx',3);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (9,'The Road Back ','Erich Maria Remarque',315,'pdf',3);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (10,'The Great Gatsby ','F. Scott Fitzgerald',218,'pdf',4);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (11,'Tender Is the Night ','F. Scott Fitzgerald',321,'pdf',4);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (12,'Hard to Be a God','Arkady and Boris Strugatsky',224,'pdf',5);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (13,'Monday Begins on Saturday','Arkady and Boris Strugatsky',331,'pdf',5);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (14,'Harry Potter and the Philosophers Stone','J. K. Rowling',309,'pdf',6);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (15,'Harry Potter and the Chamber of Secrets','J. K. Rowling',341,'pdf',6);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (16,'Harry Potter and the Prisoner of Azkaban','J. K. Rowling',435,'pdf',6);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (17,'Harry Potter and the Goblet of Fire','J. K. Rowling',735,'pdf',6);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (18,'Harry Potter and the Order of the Phoenix','J. K. Rowling',870,'pdf',6);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (19,'Harry Potter and the Half-Blood Prince','J. K. Rowling',625,'pdf',6);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (20,'Harry Potter and the Deathly Hallows','J. K. Rowling',759,'pdf',6);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (21,'Math for childrens','no Author',100,'pdf',7);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (22,'Dinosaurs','no Author',250,'pdf',8);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (23,'Steve Jobs','Walter Isaacson',656,'pdf',9);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (24,'The Business School ','Robert Kiyosaki',656,'pdf',9);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (25,'Think and Grow','Napoleon Hill',384,'pdf',10);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (26,'SONY. Made in Japan','Akio Morita',288,'pdf',11);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (27,'The Marketing Audit Handbook ','Obri Willson',368,'pdf',12);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (28,'Macroeconomics','Gregory Mankiw',560,'pdf',13);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (29,'Economics for Business: Competition, Macro-stability and Globalisation ','Dermot McAleese ',696,'pdf',13);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (30,'Fundamentals of Financial Management ','Eugene  Brigham ',592,'pdf',14);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (31,'Thinking in java ','Bruce Eccel ',638,'pdf',15);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (32,'The Java Programming Language','James Gosling',642,'pdf',15);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (33,'Code complete ','Steve McConnell ',896,'pdf',16);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (34,'Algorithms ',' Robert Sedgewick  ',848,'pdf',16);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (35,'Joomla! Programming ','Louis Landry',592,'pdf',17);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (36,'TCP/IP. Network Administration ','Craig Hunt',748,'pdf',17);
Insert into LIBRARY.BOOKS (BOOK_ID,BOOK_NAME,BOOK_AUTHOR,NUMBER_OF_PAGES,BOOK_FORMAT,GENRE_ID) values (37,'Typography. A Guide to Setting Perfect Type','Jim Felici',496,'pdf',18);
REM INSERTING into LIBRARY.GENRES
SET DEFINE OFF;
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (1,'russian classical',1);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (2,'russian modern',1);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (3,'foreign classical',1);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (4,'foreign modern',1);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (5,'fantasy',1);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (6,'belles-lettres',3);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (7,'education and development',3);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (8,'encyclopedia',3);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (9,'marketing and advertisement',2);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (10,'management',2);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (11,'enterprising',2);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (12,'audit and accounting',2);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (13,'economics',2);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (14,'finances and banking',2);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (15,'programming languages',4);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (16,'software development',4);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (17,'Internet',4);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (18,'graphics. design. multimedia.',4);
Insert into LIBRARY.GENRES (GENRE_ID,GENRE,PUBLICATION_TYPE_ID) values (19,'other',5);
REM INSERTING into LIBRARY.LIBRARY
SET DEFINE OFF;
REM INSERTING into LIBRARY.PUBLICATION_TYPE
SET DEFINE OFF;
Insert into LIBRARY.PUBLICATION_TYPE (PUBLICATION_TYPE_ID,TYPE_NAME) values (1,'belles-lettres');
Insert into LIBRARY.PUBLICATION_TYPE (PUBLICATION_TYPE_ID,TYPE_NAME) values (2,'business literature');
Insert into LIBRARY.PUBLICATION_TYPE (PUBLICATION_TYPE_ID,TYPE_NAME) values (3,'children''s literature');
Insert into LIBRARY.PUBLICATION_TYPE (PUBLICATION_TYPE_ID,TYPE_NAME) values (4,'computer & science');
Insert into LIBRARY.PUBLICATION_TYPE (PUBLICATION_TYPE_ID,TYPE_NAME) values (5,'other');
--------------------------------------------------------
--  DDL for Index PUBLICATIONS$TYPE_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "LIBRARY"."PUBLICATIONS$TYPE_PK" ON "LIBRARY"."PUBLICATION_TYPE" ("PUBLICATION_TYPE_ID") 
  ;
--------------------------------------------------------
--  DDL for Index BOOKS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "LIBRARY"."BOOKS_PK" ON "LIBRARY"."BOOKS" ("BOOK_ID") 
  ;
--------------------------------------------------------
--  DDL for Index GENRES_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "LIBRARY"."GENRES_PK" ON "LIBRARY"."GENRES" ("GENRE_ID") 
  ;
--------------------------------------------------------
--  DDL for Index LIBRARY_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "LIBRARY"."LIBRARY_PK" ON "LIBRARY"."LIBRARY" ("LIBRARY_ID") 
  ;
--------------------------------------------------------
--  Constraints for Table LIBRARY
--------------------------------------------------------

  ALTER TABLE "LIBRARY"."LIBRARY" ADD CONSTRAINT "LIBRARY_PK" PRIMARY KEY ("LIBRARY_ID") ENABLE;
 
  ALTER TABLE "LIBRARY"."LIBRARY" MODIFY ("LIBRARY_ID" NOT NULL ENABLE);
 
  ALTER TABLE "LIBRARY"."LIBRARY" MODIFY ("PUBLICATION_TYPE_ID" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table PUBLICATION_TYPE
--------------------------------------------------------

  ALTER TABLE "LIBRARY"."PUBLICATION_TYPE" ADD CONSTRAINT "PUBLICATIONS$TYPE_PK" PRIMARY KEY ("PUBLICATION_TYPE_ID") ENABLE;
 
  ALTER TABLE "LIBRARY"."PUBLICATION_TYPE" MODIFY ("PUBLICATION_TYPE_ID" NOT NULL ENABLE);
 
  ALTER TABLE "LIBRARY"."PUBLICATION_TYPE" MODIFY ("TYPE_NAME" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table GENRES
--------------------------------------------------------

  ALTER TABLE "LIBRARY"."GENRES" ADD CONSTRAINT "GENRES_PK" PRIMARY KEY ("GENRE_ID") ENABLE;
 
  ALTER TABLE "LIBRARY"."GENRES" MODIFY ("GENRE_ID" NOT NULL ENABLE);
 
  ALTER TABLE "LIBRARY"."GENRES" MODIFY ("GENRE" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table BOOKS
--------------------------------------------------------

  ALTER TABLE "LIBRARY"."BOOKS" ADD CONSTRAINT "BOOKS_PK" PRIMARY KEY ("BOOK_ID") ENABLE;
 
  ALTER TABLE "LIBRARY"."BOOKS" MODIFY ("BOOK_ID" NOT NULL ENABLE);
 
  ALTER TABLE "LIBRARY"."BOOKS" MODIFY ("BOOK_NAME" NOT NULL ENABLE);
 
  ALTER TABLE "LIBRARY"."BOOKS" MODIFY ("BOOK_AUTHOR" NOT NULL ENABLE);
--------------------------------------------------------
--  Ref Constraints for Table BOOKS
--------------------------------------------------------

  ALTER TABLE "LIBRARY"."BOOKS" ADD CONSTRAINT "BOOKS_GENRES_FK1" FOREIGN KEY ("GENRE_ID")
	  REFERENCES "LIBRARY"."GENRES" ("GENRE_ID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table GENRES
--------------------------------------------------------

  ALTER TABLE "LIBRARY"."GENRES" ADD CONSTRAINT "GENRES_PUBLICATIONS$TYPE_FK1" FOREIGN KEY ("PUBLICATION_TYPE_ID")
	  REFERENCES "LIBRARY"."PUBLICATION_TYPE" ("PUBLICATION_TYPE_ID") ENABLE;
--------------------------------------------------------
--  DDL for Trigger BI_BOOKS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "LIBRARY"."BI_BOOKS" 
  before insert on "BOOKS"               
  for each row  
begin   
    select "BOOKS_SEQ".nextval into :NEW.BOOK_ID from dual; 
end; 
/
ALTER TRIGGER "LIBRARY"."BI_BOOKS" ENABLE;
